import pandas as pd
from pymysql import connect

con = connect(user='root', password='123456', host='192.168.116.90', database='spider', port=3306,
              charset='utf8')
sql = "select * from jd_comment"
df = pd.read_sql(sql, con)

# 需求： 对jd爬取结果中用户对产品颜色偏好进行展示
productColor_res = df.groupby(by=['productColor'])['id'].count().reset_index()

# 绘制统计图形
from pyecharts.charts import Pie
import pyecharts.options as opts

pie = Pie()

pie.add(
    '饼图',
    [list(z) for z in zip(productColor_res['productColor'], productColor_res['id'])],
    radius=['40%', '75%'],  # radius 表示内环和外环半径，列表类型
    # rosetype设置玫瑰饼图模式, area所有扇区圆心角相同，仅通过半径展现数据大小
    rosetype='area'
)

pie.set_global_opts(title_opts=opts.TitleOpts(title='京东评论-产品颜色偏好'))
pie.set_series_opts(label_opts=opts.LabelOpts(formatter='{b}:{c}'))

sql = "select * from douban"
douban_df = pd.read_sql(sql, con)


def split(type):
    return str(type).split(" ")[0]


def split2(type):
    split_res = str(type).split(" ")
    if (len(split_res) > 1):
        return split_res[1]
    else:
        return None


def split3(type):
    split_res = str(type).split(" ")
    if (len(split_res) > 2):
        return split_res[2]
    else:
        return None


def split4(type):
    split_res = str(type).split(" ")
    if (len(split_res) > 3):
        return split_res[3]
    else:
        return None


def split5(type):
    split_res = str(type).split(" ")
    if (len(split_res) > 4):
        return split_res[4]
    else:
        return None


# 剧情 爱情 同性
# 剧情 爱情

douban_df1 = douban_df.movie_type.map(split).reset_index()
douban_df2 = douban_df.movie_type.map(split2).reset_index()
douban_df3 = douban_df.movie_type.map(split3).reset_index()
douban_df4 = douban_df.movie_type.map(split4).reset_index()
douban_df5 = douban_df.movie_type.map(split5).reset_index()

douban_total = douban_df1.append(douban_df2).append(douban_df3).append(douban_df4).append(douban_df5)

douban_total.dropna(subset=['movie_type'], inplace=True)

douban_total = douban_total.groupby(by=['movie_type'])['index'].count().reset_index()

from pyecharts.charts import Bar

bar = Bar()
x = list(douban_total['movie_type'])
y = list(douban_total['index'])
bar.add_xaxis(x)
bar.add_yaxis('类型', y)
bar.render('类型.html')
pie.render('dv.html')

from pyecharts.charts import Page

page = Page(layout=Page.SimplePageLayout)  # 实例化page类
page.add(bar)
page.add(pie)
page.render("一个页面渲染多张图表.html")
print("生成成功")
print("执行成功")
